from sqlalchemy import create_engine
import pymysql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
# 外键管理依赖
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

pymysql.install_as_MySQLdb()  # MySQL驱动
# 数据库连接URL格式: 数据库类型://用户名:密码@ip:端口/数据库名称
engine = create_engine('mysql://root:root@127.0.0.1:3306/test', echo=True)  # 数据库连接对象
Session = sessionmaker(bind=engine)  # 会话
session = Session()  # 会话对象
Base = declarative_base()  # 模型基类


class User(Base):
    """
    用户表
    """
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(24))  # 姓名,字符串必须要指定长度
    fullname = Column(String(24))  # 全名
    nickname = Column(String(24))  # 昵称

    def __repr__(self):
        return "User(id='%s', name='%s')" % (
            self.id, self.name)


class Address(Base):
    """
    地址表
    """
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String(128), nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))  # 外键
    user = relationship("User", back_populates="addresses")  # 建立关联关系

    def __repr__(self):
        return "Address(email_address='%s')" % self.email_address


# 建立反向关联
User.addresses = relationship("Address", order_by=Address.id, back_populates="user")

# 子查询
# func 关键字生成SQL函数
from sqlalchemy.sql import func

stmt = session.query(Address.user_id, func.count('*'). \
                     label('address_count')). \
    group_by(Address.user_id).subquery()
for u, count in session.query(User, stmt.c.address_count). \
        outerjoin(stmt, User.id == stmt.c.user_id).order_by(User.id):
    print(u, count)
